# -*- encoding:utf-8 -*-
# @Date  :2018-10-31
# @Author:田国新（tianguoxin#antiy.cn）

# 角色同步

import json
import warnings
import urllib3
from flask import Blueprint, g
from psycopg2 import connect
from psycopg2.extras import RealDictCursor

from config import config
from lib import auth
from lib.table import table
from lib.validform import V, ValidateForm
from lib.funcs import validateAnguan

urllib3.disable_warnings()
warnings.filterwarnings("ignore")

app = Blueprint(__name__ + "_app", __name__)


@app.before_request
def setupdb():
    g.conn = connect(**config.DatabaseConfig.siem)
    g.cursor = g.conn.cursor(cursor_factory=RealDictCursor)


@app.teardown_request
def unsetdb(exception):
    if g.cursor:
        g.cursor.close()
        g.cursor = None
    if g.conn:
        g.conn.close()
        g.conn = None


@app.route('/api/sync/role/add', methods=['POST'])
def role_add():
    """新增角色同步 单条"""
    if validateAnguan()=='fail':
        return json.dumps({'status': 'E1','msg': '未通过安全校验'})
    form = ValidateForm(
        role_id=['角色编号', V.required(),V.maxlength(30)],
        role_name=['角色名称', V.required(),V.maxlength(30)],
        group_ids=['菜单项编号', V.optional()],
        r_person=['角色创建人ID', V.required(),V.maxlength(30)],
    )
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'E3'
        data['msg'] = {"errorKey": data.pop("_key"), "message": data["msg"]}
        return json.dumps(data)

    try:
        """校验角色编号"""
        g.cursor.execute("""select role_id
                            from sys_role
                            where state = '1' and role_id = '%s'""" % data["role_id"])
        if g.cursor.fetchone():
            return json.dumps({"status": "E2", "msg": {"errorKey": "role_id", "message": "新增角色 角色编号已存在"}})

        """校验角色名称"""
        g.cursor.execute("""select role_name
                            from sys_role
                            where state = '1' and role_name = '%s'""" % data["role_name"])
        if g.cursor.fetchone():
            return json.dumps({"status": "E2", "msg": {"errorKey": "role_name", "message": "新增角色 角色名称已存在"}})

        """校验菜单项"""
        if len(data["group_ids"])>0:
            g.cursor.execute("""select group_id
                            from sys_function_group
                            where state = '1' and
                            group_id in (select unnest(array%s))""" % json.dumps(data['group_ids']).replace("\"","'"))
            if len(g.cursor.fetchall()) != len(data["group_ids"]):
                return json.dumps({"status": "E3", "msg": {"errorKey": "group_ids", "message": "新增角色 菜单项存在差异"}})

        """校验角色创建人ID"""
        g.cursor.execute("""select person_id
                            from sys_person
                            where state = '1' and person_id = '%s'""" % data["r_person"])
        if not g.cursor.fetchone():
            return json.dumps({"status": "E3", "msg": {"errorKey": "r_person", "message": "新增角色 角色创建人不存在"}})

        """校验完毕 开始插入"""
        g.cursor.execute(
            """insert into sys_role(role_id, role_name, data_source, r_person, update_time)
                            VALUES (%s, %s, '0', %s, now())""", (data["role_id"], data["role_name"], data["r_person"]))
        if len(data["group_ids"])>0:
            g.cursor.execute("""INSERT into h_role_synchroniz_info (role_id,role_name_change,function_group_change)
                            select '%s' as role_id,'%s' as role_name_change,array_to_string(array_agg(a.cn_name),',') as function_group_change
                            from sys_function_group a
                            where group_id in (select unnest (array%s)) group by role_id,role_name_change""" % (data['role_id'],data['role_name'],json.dumps(data['group_ids']).replace("\"","'")))
        else:
            g.cursor.execute("""INSERT into h_role_synchroniz_info (role_id,role_name_change) values('%s','%s')""" % (data['role_id'],data['role_name']))
        g.conn.commit()
        
        for group_id in data["group_ids"]:
            info = {
                "group_id": group_id,
                "role_id": data["role_id"],
                "is_readonly": '0'
            }
            g.cursor.execute(
                """insert into sys_role_function(group_id, role_id, read_only_state, data_source)
                                VALUES (%(group_id)s, %(role_id)s, %(is_readonly)s, '0')""", info)
        g.conn.commit()
        return json.dumps({"status": "S0"})
    except Exception, e:
        return json.dumps({"status": "E0", "msg": json.dumps(data),'s':str(e)})


@app.route('/api/sync/role/update', methods=['POST'])
def role_update():
    """修改角色同步 单条"""
    if validateAnguan()=='fail':
        return json.dumps({'status': 'E1','msg': '未通过安全校验'})
    form = ValidateForm(
        role_id=['角色编号', V.optional(),V.maxlength(30)],
        role_name=['角色名称', V.required(),V.maxlength(30)],
        group_ids=['菜单项编号', V.optional()],
        r_person=['角色修改人ID', V.required(),V.maxlength(30)]
    )
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'E3'
        data['msg'] = {"errorKey": data.pop("_key"), "message": data["msg"]}
        return json.dumps(data)
    try:
        """校验角色编号"""
        g.cursor.execute("""select role_id
                            from sys_role
                            where state = '1' and role_id = '%s'""" % data["role_id"])
        if not g.cursor.fetchall():
            return json.dumps({"status": "E3", "msg": {"errorKey": "role_id", "message": "修改角色 角色编号不存在"}})

        """校验角色名称"""
        g.cursor.execute("""select role_name
                            from sys_role
                            where state = '1' and role_name = '%s'""" % data["role_name"])
        if g.cursor.fetchone():
            g.cursor.execute("""select role_name
                                from sys_role
                                where state = '1' and role_id = '%s' 
                                and role_name = '%s'""" % (data["role_id"], data["role_name"]))
            if not g.cursor.fetchone():
                return json.dumps({"status": "E2", "msg": {"errorKey": "role_name", "message": "修改角色 角色名称已存在"}})
            

        """校验菜单项"""
        if len(data["group_ids"])>0:
            print len(data["group_ids"])
            g.cursor.execute("""select group_id
                            from sys_function_group
                            where state = '1' and
                            group_id in (select unnest(array%s))""" % json.dumps(data['group_ids']).replace("\"","'"))
            if len(g.cursor.fetchall()) != len(data["group_ids"]):
                return json.dumps({"status": "E3", "msg": {"errorKey": "group_ids", "message": "修改角色 菜单项存在差异"}})

        """校验角色创建人ID"""
        g.cursor.execute("""select person_id
                            from sys_person
                            where state = '1' and person_id = '%s'""" % data["r_person"])
        if not g.cursor.fetchone():
            return json.dumps({"status": "E3", "msg": {"errorKey": "r_person", "message": "修改角色 角色创建人不存在"}})

        g.cursor.execute("""update sys_role set role_name = %s, update_time = now(), r_person = %s, data_source = '0'
                            where role_id = %s""", (data["role_name"], data["r_person"], data["role_id"]))
        g.cursor.execute("""delete from sys_role_function where role_id = '%s'""" % data["role_id"])
        if len(data["group_ids"])>0:
            g.cursor.execute("""INSERT into h_role_synchroniz_info (role_id,role_name_change,function_group_change)
                            select '%s' as role_id,'%s' as role_name_change,array_to_string(array_agg(a.cn_name),',') as function_group_change
                            from sys_function_group a
                            where group_id in (select unnest (array%s)) group by role_id,role_name_change""" % (data['role_id'],data['role_name'],json.dumps(data['group_ids']).replace("\"","'")))
        else:
            g.cursor.execute("""INSERT into h_role_synchroniz_info (role_id,role_name_change) values('%s','%s')""" % (data['role_id'],data['role_name']))
        g.conn.commit()
        
        for group_id in data["group_ids"]:
            info = {
                "group_id": group_id,
                "role_id": data["role_id"],
                "is_readonly": '0'
            }
            g.cursor.execute("""insert into sys_role_function(group_id, role_id, read_only_state, data_source)
                                VALUES (%(group_id)s, %(role_id)s, %(is_readonly)s, '0')""", info)
        g.conn.commit()

        return json.dumps({"status": "S0"})
    except Exception, e:
        return json.dumps({"status": "E0", "msg": json.dumps(data)})


@app.route('/api/sync/role/delete', methods=['POST'])
def role_delete():
    if validateAnguan()=='fail':
        return json.dumps({'status': 'E1','msg': '未通过安全校验'})
    """删除角色同步 单条"""
    form = ValidateForm(
        role_id=['角色ID', V.required(),V.maxlength(30)],
        r_person=['操作用户', V.required(),V.maxlength(30)]
    )
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'E3'
        data['msg'] = {"errorKey": data.pop("_key"), "message": data["msg"]}
        return json.dumps(data)
    try:
        # 判断角色是否存在
        g.cursor.execute("""select role_id
                            from sys_role
                            where state = '1' and role_id = '%s'""" % data["role_id"])
        if not g.cursor.fetchone():
            return json.dumps({"status": "E3", "msg": {"errorKey": "role_id", "message": "删除角色 角色编号不存在"}})

        """校验操作人ID"""
        g.cursor.execute("""select person_id
                            from sys_person
                            where state = '1' and person_id = '%s'""" % data["r_person"])
        if not g.cursor.fetchone():
            return json.dumps({"status": "E3", "msg": {"errorKey": "r_person", "message": "删除角色 操作用户不存在"}})

        g.cursor.execute(
            """update sys_role set state = '0', update_time = now(), data_source = '0' WHERE role_id = '%s'""" % data["role_id"])
        g.cursor.execute("""SELECT * from sys_role_function where role_id = %(role_id)s""",(data))
        role_funcs = g.cursor.fetchall()
        if len(role_funcs)>0:
            g.cursor.execute("""INSERT into h_role_synchroniz_info (role_id,role_name_change,function_group_change)
                            select %(role_id)s as role_id,(select role_name from sys_role where role_id = %(role_id)s) as role_name_change,array_to_string(array_agg(a.cn_name),',') as function_group_change
                            from sys_function_group a
                            where group_id in (select group_id from sys_role_function where role_id = %(role_id)s)
                            group by role_id,role_name_change""" , (data))
        else:
            g.cursor.execute("""INSERT into h_role_synchroniz_info (role_id,role_name_change)
                                values(%(role_id)s,(select role_name from sys_role where role_id = %(role_id)s))""", (data))
        g.conn.commit()
        return json.dumps({"status": "S0"})
    except Exception, e:
        return json.dumps({"status": "E0", "msg": json.dumps(data)})


@app.route('/api/sync/role/num', methods=['POST'])
@auth.permission('role', "readOnly")
def role_sync_num(_currUser):
    """同步信息 数量"""
    g.cursor.execute("""select count(1) as num
                        from h_synchroniz_info
                        where synchroniz_table = 'sys_role'
                        and person_id = '%s'
                        and state = '1'""" % _currUser["user"]["person_id"])
    return json.dumps(
        {
            "status": "success",
            "data": g.cursor.fetchone()
        }
    )


@app.route('/api/sync/role/clear', methods=['POST'])
@auth.permission('role', "readOnly")
def role_sync_clear(_currUser):
    """查看同步信息后 清除计数"""
    try:
        g.cursor.execute("""update h_synchroniz_info set state = '0'
                            where person_id = '%s'
                            and synchroniz_table = 'sys_role'""" % _currUser["user"]["person_id"])
        g.conn.commit()
        return json.dumps({"status": "success"})                    
    except Exception, e:
        return json.dumps({"status": "fail", "msg": "同步信息清除计数成功"})


@app.route('/api/sync/role/list/add', methods=['POST'])
@auth.permission('role', "readOnly")
def role_sync_list_add(_currUser):
    """同步信息 新增列表详细信息"""
    form = ValidateForm(
        order=['排序', V.required()]
    )
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'fail'
        return json.dumps(data)

    g.cursor.execute("""SELECT d2t(a.r_time) as ar_time, b.role_id, b.role_name_change as role_name, a.operating, b.function_group_change as cn_name
                        from h_synchroniz_info a
                        left join h_role_synchroniz_info b on a.r_time = b.r_time
                        where a.person_id = '%s' and a.synchroniz_table = 'sys_role' and a.operating = 'add'
                        and a.state = '1'
                        group by b.role_id, b.role_name_change, a.operating, a.r_time, b.function_group_change 
                        order by a.r_time %s""" % (_currUser["user"]["person_id"], data["order"]))

    return json.dumps(
        {
            "status": "success",
            "data": g.cursor.fetchall()
        }
    )


@app.route('/api/sync/role/list/update', methods=['POST'])
@auth.permission('role', "readOnly")
def role_sync_list_update(_currUser):
    """同步信息 修改列表详细信息"""
    form = ValidateForm(
        order=['排序', V.required()]
    )
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'fail'
        return json.dumps(data)

    g.cursor.execute("""SELECT d2t(a.r_time) as ar_time, b.role_id, b.role_name_change as role_name, a.operating, b.function_group_change as cn_name
                        from h_synchroniz_info a
                        left join h_role_synchroniz_info b on a.r_time = b.r_time
                        where a.person_id = '%s' and a.synchroniz_table = 'sys_role' and a.operating = 'update'
                        and a.state = '1'
                        group by b.role_id, b.role_name_change, a.operating, a.r_time, b.function_group_change 
                        order by a.r_time %s""" % (_currUser["user"]["person_id"], data["order"]))

    return json.dumps(
        {
            "status": "success",
            "data": g.cursor.fetchall()
        }
    )


@app.route('/api/sync/role/list/delete', methods=['POST'])
@auth.permission('role', "readOnly")
def role_sync_list_delete(_currUser):
    """同步信息 删除列表详细信息"""
    form = ValidateForm(
        order=['排序', V.required()]
    )
    (flag, data) = form.validate()
    if not flag:
        data['status'] = 'fail'
        return json.dumps(data)

    g.cursor.execute("""SELECT d2t(a.r_time) as ar_time, b.role_id, b.role_name_change as role_name, a.operating, b.function_group_change as cn_name
                        from h_synchroniz_info a
                        left join h_role_synchroniz_info b on a.r_time = b.r_time
                        where a.person_id = '%s' and a.synchroniz_table = 'sys_role' and a.operating = 'delete'
                        and a.state = '1'
                        group by b.role_id, b.role_name_change, a.operating, a.r_time, b.function_group_change 
                        order by a.r_time %s""" % (_currUser["user"]["person_id"], data["order"]))

    return json.dumps(
        {
            "status": "success",
            "data": g.cursor.fetchall()
        }
    )


@app.route('/api/sync/role/history/add', methods=['POST'])
@auth.permission('role', "readOnly")
def role_sync_history_add(_currUser):
    """查看 同步历史 - 新增 信息"""
    tb = table()
    _where = """a.synchroniz_table = 'sys_role' and a.person_id = '%s'
                and a.state = '0' and a.operating = 'add'""" % _currUser["user"]["person_id"]

    g.cursor.execute("""select count(1) as num from
                        (
                          select d2t(max(a.r_time)) as ar_time, b.role_id as brole_id,
                               b.role_name as brole_name, b.function_group_change as dcn_name
                          from h_synchroniz_info a
                          left join (select role_name_change as role_name,role_id,function_group_change,r_time from h_role_synchroniz_info) b on a.r_time = b.r_time
                          %s
                          group by b.role_id, b.role_name,b.function_group_change
                        ) a
                        """ % (tb.where(['b.role_name'], _where)))
    total = g.cursor.fetchone()["num"]

    g.cursor.execute("""select d2t(max(a.r_time)) as ar_time, b.role_id as brole_id,
                               b.role_name as brole_name, b.function_group_change as dcn_name
                        from h_synchroniz_info a
                        left join (select role_name_change as role_name,role_id,function_group_change,r_time  from h_role_synchroniz_info) b on a.r_time = b.r_time
                        %s
                        group by b.role_id, b.role_name,b.function_group_change
                        %s %s %s""" % (tb.where(['b.role_name'], _where), tb.orderBy().replace("a.r_time", "ar_time"), tb.offset(), tb.limit()))

    return json.dumps({
        "status": "success",
        "data": {
            "current": tb.current(),
            "total": total,
            "dataSource": g.cursor.fetchall()
        }
    })


@app.route('/api/sync/role/history/update', methods=['POST'])
@auth.permission('role', "readOnly")
def role_sync_history_update(_currUser):
    """查看 同步历史 - 修改 信息"""
    tb = table()
    _where = """a.synchroniz_table = 'sys_role' and a.person_id = '%s'
                and a.state = '0' and a.operating = 'update'""" % _currUser["user"]["person_id"]

    g.cursor.execute("""select count(1) as num from
                        (
                          select d2t(max(a.r_time)) as ar_time, b.role_id as brole_id,
                               b.role_name as brole_name, b.function_group_change as dcn_name
                          from h_synchroniz_info a
                          left join (select role_name_change as role_name,role_id,function_group_change,r_time  from  h_role_synchroniz_info) b on a.r_time = b.r_time
                          %s
                          group by b.role_id, b.role_name,b.function_group_change
                        ) a
                        """ % (tb.where(['b.role_name'], _where)))
    total = g.cursor.fetchone()["num"]

    g.cursor.execute("""select d2t(max(a.r_time)) as ar_time, b.role_id as brole_id,
                               b.role_name as brole_name, b.function_group_change as dcn_name
                        from h_synchroniz_info a
                        left join (select role_name_change as role_name,role_id,function_group_change,r_time  from  h_role_synchroniz_info) b on a.r_time = b.r_time
                        %s
                        group by b.role_id, b.role_name,b.function_group_change
                        %s %s %s""" % (tb.where(['b.role_name'], _where), tb.orderBy().replace("a.r_time", "ar_time"), tb.offset(), tb.limit()))


    return json.dumps({
        "status": "success",
        "data": {
            "current": tb.current(),
            "total": total,
            "dataSource": g.cursor.fetchall()
        }
    })


@app.route('/api/sync/role/history/delete', methods=['POST'])
@auth.permission('role', "readOnly")
def role_sync_history_delete(_currUser):
    """查看 同步历史 - 删除 信息"""
    tb = table()
    _where = """a.synchroniz_table = 'sys_role' and a.person_id = '%s'
                and a.state = '0' and a.operating = 'delete'""" % _currUser["user"]["person_id"]

    g.cursor.execute("""select count(1) as num from
                        (
                          select d2t(max(a.r_time)) as ar_time, b.role_id as brole_id,
                               b.role_name as brole_name, b.function_group_change as dcn_name
                          from h_synchroniz_info a
                          left join (select role_name_change as role_name,role_id,function_group_change,r_time  from  h_role_synchroniz_info) b on a.r_time = b.r_time
                          %s
                          group by b.role_id, b.role_name,b.function_group_change
                        ) a
                        """ % (tb.where(['b.role_name'], _where)))
    total = g.cursor.fetchone()["num"]

    g.cursor.execute("""select d2t(max(a.r_time)) as ar_time, b.role_id as brole_id,
                               b.role_name as brole_name, b.function_group_change as dcn_name
                        from h_synchroniz_info a
                        left join (select role_name_change as role_name,role_id,function_group_change,r_time  from  h_role_synchroniz_info) b on a.r_time = b.r_time
                        %s
                        group by b.role_id, b.role_name,b.function_group_change
                        %s %s %s""" % (tb.where(['b.role_name'], _where), tb.orderBy().replace("a.r_time", "ar_time"), tb.offset(), tb.limit()))

    return json.dumps({
        "status": "success",
        "data": {
            "current": tb.current(),
            "total": total,
            "dataSource": g.cursor.fetchall()
        }
    })